﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
//using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace SuperSqlOperation.Sqlite
{
    static class DeleteSqlExecute
    {
        /// <summary>
        /// 全表删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public static async Task<int> ClearExecute<T>(this IDbConnection conn)
        {
           // string tableName = model.GetType().GetDataTableNameByAttr();
            Type t = typeof(T);
            PropertyInfo[] propertyInfo = t.GetProperties();
            string tableName = t.GetDataTableNameByAttr();

            string sqlCommandText = "DELETE FROM " + tableName;

            int insertId = 0;
            using (conn)
            {
                try
                {
                    SQLiteCommand comm = conn.CreateCommand() as SQLiteCommand;
                    comm.CommandText = sqlCommandText;

                    conn.Open();
                    //insertId = await Task.Run(() => comm.ExecuteNonQuery());
                    insertId = await comm.ExecuteNonQueryAsync();
                    conn.Close();
                }
                catch (Exception)
                {
                    conn.Close();
                    insertId = -404;
                } 
            }
            return insertId;
        }

        /// <summary>
        /// 删除某一数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public static async Task<int> DeleteExecute<T>(this IDbConnection conn, T model)
        {

            string tableName = model.GetType().GetDataTableNameByAttr();
            PropertyInfo[] propertyInfo = model.GetType().GetProperties();
            StringBuilder whereStr = new StringBuilder();
            List<SQLiteParameter> par = new List<SQLiteParameter>();



            string linkStr = " AND ";

            foreach (PropertyInfo property in propertyInfo)
            {
                if (property.GetDataIgnoreByAttr()) continue;  //过滤掉特性定义的需要忽略的属性
                if (!property.GetDataWriteByAttr()) continue;  //过滤掉特性定义的是否可写的属性 定义为false则忽略 
                if (property.PropertyType.IsConstructedGenericType ||
                    (!property.PropertyType.IsPrimitive && !property.PropertyType.Equals(typeof(string)) &&
                    property.PropertyType.IsClass)) continue;

                string columnName = property.GetDataColumnByAttr();
                var columnValue = property.GetValue(model) == null ? DBNull.Value : property.GetValue(model);

                whereStr.Append(columnName + "= @" + columnName + linkStr);
                par.Add(new SQLiteParameter("@" + columnName, columnValue));

            }

            string sqlCommandText = string.Format("DELETE FROM {0} WHERE {1}",
                    tableName,
                    whereStr.ToString().Substring(0, whereStr.ToString().LastIndexOf(linkStr))); 

            int insertId = 0;
            using (conn)
            {
                try
                {
                    SQLiteCommand comm = conn.CreateCommand() as SQLiteCommand;
                    comm.CommandText = sqlCommandText;
                    comm.Parameters.AddRange(par.ToArray());

                    conn.Open();
                    //insertId = await Task.Run(() => comm.ExecuteNonQuery());
                    insertId = await comm.ExecuteNonQueryAsync();
                    conn.Close(); 
                }
                catch (Exception)
                {
                    conn.Close();
                    insertId = -404 ;
                } 
            }
            return insertId;

        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="model"></param>
        /// <param name="conds"></param>
        /// <param name="linkStr"></param>
        /// <returns></returns>
        public static async Task<int> DeleteExecute<T>(this IDbConnection conn,  Dictionary<string, object> conds, string linkStr)
        {
            linkStr = " " + linkStr + " ";

            //string tableName = model.GetType().GetDataTableNameByAttr();
            //PropertyInfo[] propertyInfo = model.GetType().GetProperties();

            Type t = typeof(T);
            PropertyInfo[] propertyInfo = t.GetProperties();
            string tableName = t.GetDataTableNameByAttr();

            StringBuilder whereStr = new StringBuilder();
            List<SQLiteParameter> par = new List<SQLiteParameter>();
              
            foreach (var cond in conds)
            {
                string columnName = cond.Key;
                object columnValue = cond.Value;
                whereStr.Append(columnName + "= @" + columnName + linkStr);
                par.Add(new SQLiteParameter("@" + columnName, columnValue));
            }

            string sqlCommandText =
                    string.Format("DELETE FROM {0} WHERE {1}",
                    tableName,
                    whereStr.ToString().Substring(0, whereStr.ToString().LastIndexOf(linkStr)));

            int insertId = 0;
            using (conn)
            {
                try
                {
                    SQLiteCommand comm = conn.CreateCommand() as SQLiteCommand;
                    comm.CommandText = sqlCommandText;
                    comm.Parameters.AddRange(par.ToArray());

                    conn.Open();
                    //insertId = await Task.Run(() => comm.ExecuteNonQuery());
                    insertId = await comm.ExecuteNonQueryAsync();
                    conn.Close();
                }
                catch (Exception)
                {
                    conn.Close();
                    insertId = -404;
                } 
            }
            return insertId;

        }
         
    }
}
